<?php
/**
 * report.php
 * @author ranuandra
 *
 * 12 Agu 2011 15:14:48
 *
 * 
 */

class Model_Report extends Model
{
	
	
	public function data_by_customer($post)
	{
		$model = array();
		$types = array();
		$merks = array();
		$areas = array();
		
		if(isset($post['model_id']) && $post['model_id']!='null')
		{
			$models = $post['model_id'];
		}
		if(isset($post['type_id']) && $post['type_id']!='null')
		{
			$types = $post['type_id'];
		}
		
		if(isset($post['merk_id']) && $post['merk_id']!='null')
		{
			$merks = $post['merk_id'];
		}
		
		if(isset($post['area_id']) && $post['area_id']!='null' )
		{
			$areas = $post['area_id'];
		}
		
		$sub = DB::select(array('COUNT("polregs.id")', 'jumlah'))->from('polregs')
			  ->join('types', 'inner')->on('types.id', '=', 'polregs.type_id');

		if(isset($post['model_id']))
		{
			if(count($models) > 0)
			{
				$sub = $sub->where_open();
				foreach($models as $model)
				{
					if($model > 0)
					{
							$sub  = $sub ->or_where('types.model_id', '=', $model);
					}
				}
				$sub = $sub ->or_where_close();
			}
		}
		
		if(isset($post['merk_id']))
		{
			if(count($merks) > 0)
			{
				$sub  = $sub ->where_open();
				foreach($merks as $merk)
				{
					if($merk > 0)
					{
							$sub = $sub ->or_where('types.merk_id', '=', $merk);
					}
				}
				$sub  = $sub->or_where_close();
			}
		}
		
		if(isset($post['type_id']))
		{
			if(count($types) > 0)
			{
				$sub = $sub->where_open();
				foreach($types as $type)
				{
					if($types > 0)
					{
							$sub  = $sub->or_where('polregs.type_id', '=', $type);
					}
				}
				$sub  = $sub->or_where_close();
			}
		}
		
		if(isset($post['drthn']) && isset($post['kethn']))
		{
			if($post['drthn']!="" && $post['kethn']!="" )
			{
				$sub = $sub->and_where_open();
				$sub = $sub->and_where('polregs.tgl_daftar','BETWEEN',array($post['drthn'], $post['kethn']));
				$sub = $sub->and_where_close();
			}
		}
		
		$sub = $sub->and_where_open();
		
		$sub = $sub->and_where('polregs.customer_id', '=', DB::expr('customers.id'))
				   ->and_where_close()
				   ->group_by('polregs.customer_id');
				   
		$query = DB::select('customers.id', 'areas.area', 'customers.nama','customers.alamat', array($sub, 'jumlah'))->from('customers')
						->join('polregs', 'inner')->on('polregs.customer_id', '=', 'customers.id')
						->join('types', 'inner')->on('types.id', '=', 'polregs.type_id')
						->join('merks', 'inner')->on('merks.id', '=', 'types.merk_id')
						->join('models', 'inner')->on('models.id', '=', 'types.model_id')
						->join('areas', 'inner')->on('areas.id', '=', 'polregs.area_id');
		
		//$query = $query->where('merks.id', '=', $post['merk']);
		if(isset($post['model_id']))
		{
			if(count($models) > 0)
			{
				$query = $query->where_open();
				foreach($models as $model)
				{
					if($model > 0)
					{
							$query = $query->or_where('types.model_id', '=', $model);
					}
				}
				$query = $query->or_where_close();
			}
		}
		
		if(isset($post['merk']))
		{
			if(count($merks) > 0)
			{
				$query = $query->where_open();
				foreach($merks as $merk)
				{
					if($merk > 0)
					{
							$query = $query->or_where('types.merk_id', '=', $merk);
					}
				}
				$query = $query->or_where_close();
			}
		}
		
		if(isset($post['type_id']))
		{
			if(count($types) > 0)
			{
				$query = $query->where_open();
				foreach($types as $type)
				{
					if($types > 0)
					{
							$query = $query->or_where('polregs.type_id', '=', $type);
					}
				}
				$query = $query->or_where_close();
			}
		}
		
		if(isset($post['area_id']))
		{
			//echo $area;
			if(count($areas) > 0)
			{
				$query = $query->where_open();
				if(count($areas) > 0)
				{
					foreach($areas as $area)
					{
						$query = $query->or_where('customers.area_id','=',$area);
					}
				}
				$query = $query->or_where_close();
			}
								//->order_by('jumlah', 'DESC');
/*			$query = DB::select(array('customers.id', 'customer_id'), 'customers.nama','customers.alamat',array('COUNT("polregs.customer_id")', 'jumlah'))->from('polregs')
								->join('customers')->on('customers.id','=','polregs.customers_id')
								->where('polregs.area_id', '=', $area)
								->order_by('jumlah', 'DESC');*/
		}
		if(isset($post['drthn']) && isset($post['kethn']))
		{
			if($post['drthn']!="" && $post['kethn']!="" )
			{
				$query = $query->and_where_open();
				$query = $query->and_where('polregs.tgl_daftar','BETWEEN',array($post['drthn'], $post['kethn']));
				$query = $query->and_where_close();
			}
		}
		if($post['jumlah']!="")
		{
			$query = $query->where($sub,'>=',$post['jumlah']);
		}
		

		
		$query = $query->group_by('customers.id, customers.nama, customers.alamat, areas.area');
		$query = $query->order_by('jumlah' , 'DESC');
		//print_r($query);

		return $query->execute();	
		
	}
	
	public function data_by_type($post)
	{
		$model = array();
		$types = array();
		$merks = array();
		$areas = array();
		
		if(isset($post['model_id']) && $post['model_id']!='null')
		{
			$models = $post['model_id'];
		}
		if(isset($post['type_id']) && $post['type_id']!='null')
		{
			$types = $post['type_id'];
		}
		
		if(isset($post['merk']) && $post['merk']!='null')
		{
			$merks = $post['merk'];
		}
		
		if(isset($post['area_id']) && $post['area_id']!='null' )
		{
			$areas = $post['area_id'];
		}
		
		$sub = DB::select(array('COUNT("polregs.id")', 'jumlah'))->from('polregs');
		
		$sub = $sub->and_where_open();
		
		$sub = $sub->and_where('polregs.customer_id', '=', DB::expr('customers.id'))
				   ->and_where('polregs.type_id', '=', DB::expr('types.id'))
				   ->and_where_close()
				   ->group_by('polregs.customer_id');
				   
		$query = DB::select('customers.id', 'customers.nama','customers.alamat', 'types.type', 'merks.merk', 'models.model', array($sub, 'jumlah'))->from('customers')
						->join('polregs', 'inner')->on('polregs.customer_id', '=', 'customers.id')
						->join('types', 'inner')->on('types.id', '=', 'polregs.type_id')
						->join('merks', 'inner')->on('merks.id', '=', 'types.merk_id')
						->join('models', 'inner')->on('models.id', '=', 'types.model_id');
		
		//$query = $query->where('merks.id', '=', $post['merk']);
		if(isset($post['model_id']))
		{
			if(count($models) > 0)
			{
				$query = $query->where_open();
				foreach($models as $model)
				{
					if($model > 0)
					{
							$query = $query->or_where('types.model_id', '=', $model);
					}
				}
				$query = $query->or_where_close();
			}
		}
		
		if(isset($post['merk']))
		{
			if(count($merks) > 0)
			{
				$query = $query->where_open();
				foreach($merks as $merk)
				{
					if($merk > 0)
					{
							$query = $query->or_where('types.merk_id', '=', $merk);
					}
				}
				$query = $query->or_where_close();
			}
		}
		
		if(isset($post['type_id']))
		{
			if(count($types) > 0)
			{
				$query = $query->where_open();
				foreach($types as $type)
				{
					if($types > 0)
					{
							$query = $query->or_where('polregs.type_id', '=', $type);
					}
				}
				$query = $query->or_where_close();
			}
		}
		
		if(isset($post['area_id']))
		{
			//echo $area;
			if(count($areas) > 0)
			{
				$query = $query->where_open();
				if(count($areas) > 0)
				{
					foreach($areas as $area)
					{
						$query = $query->or_where('customers.area_id','=',$area);
					}
				}
				$query = $query->or_where_close();
			}
								//->order_by('jumlah', 'DESC');
/*			$query = DB::select(array('customers.id', 'customer_id'), 'customers.nama','customers.alamat',array('COUNT("polregs.customer_id")', 'jumlah'))->from('polregs')
								->join('customers')->on('customers.id','=','polregs.customers_id')
								->where('polregs.area_id', '=', $area)
								->order_by('jumlah', 'DESC');*/
		}
		if(isset($post['drthn']) && isset($post['kethn']))
		{
			if($post['drthn']!="" && $post['kethn']!="" )
			{
				list($dbln, $dtgl, $dthn) = explode('/', $post['drthn']);
				list($kbln, $ktgl, $kthn) = explode('/', $post['kethn']);
				
				$tgl1 = $dthn.'-'.$dbln.'-'.$dtgl;
				$tgl2 = $kthn.'-'.$kbln.'-'.$ktgl;
				
				$query = $query->and_where_open();
				$query = $query->and_where('tgl_daftar','BETWEEN',array($tgl1, $tgl2));
				$query = $query->and_where_close();
			}
		}
		if($post['jumlah']!="")
		{
			$query = $query->where($sub,'>=',$post['jumlah']);
		}
		

		
		$query = $query->group_by('customers.id, types.id, customers.nama, customers.alamat, types.type, merks.merk, models.model');
		$query = $query->order_by('jumlah', 'DESC');
		//print_r($query);
		return $query->execute();		
	}
	
	public function detail_customer($post, $tahun)
	{
		$models = array();
		$types = array();
		$merks = array();
		$areas = array();
		
		if(isset($post['model_id']) && $post['model_id']!='null')
		{
			if(is_array($post['model_id']))
			{
				$models = $post['model_id'];
			}
			else 
			{
				$models = explode(',', $post['model_id']);
			}
		}
		if(isset($post['type_id']) && $post['type_id']!='null')
		{
			if(is_array($post['type_id']))
			{
				$types = $post['type_id'];
			}
			else 
			{
				$types = split(',', $post['type_id']);
			}
		}
		
		if(isset($post['merk']) && $post['merk']!='null')
		{
			if(is_array($post['merk']))
			{
				$merks = $post['merk'];
			}
			else 
			{
				$merks = split(',', $post['merk']);
			}
		}
		
		if(isset($post['area_id']) && $post['area_id']!='null' )
		{
			if(is_array($post['area_id']))
			{
				$areas = $post['area_id'];
			}
			else 
			{
				$areas = split(',', $post['area_id']);
			}
		}
		
		
		
		$sub = DB::select(array('COUNT("polregs.id")', 'jumlah'))->from('polregs')
			  ->where('polregs.type_id', '=', DB::expr('t.id'));
			  
		if($tahun == 0)
		{		
			if(isset($post['drthn']) && isset($post['kethn']))
			{
				if($post['drthn']!="" && $post['kethn']!="" )
				{
					list($d, $m, $y) = preg_split('/\//', $_POST['drthn']); 
					list($d1, $m1, $y1) = preg_split('/\//', $_POST['kethn']);
					$sub = $sub->and_where_open();
					$sub = $sub->and_where(DB::expr('YEAR(polregs.tgl_daftar)'),'BETWEEN',array($y, $y1));
					$sub = $sub->and_where_close();
				}
			}
		}
		else 
		{
			$sub = $sub->where(DB::expr('YEAR(tgl_daftar)'), '=', $tahun);
		}
			  
		//$sub = $sub->where(DB::expr('YEAR(tgl_daftar)'), '=', $tahun);
		
		$sub = $sub->and_where_open();
		
		$sub = $sub->and_where('polregs.customer_id', '=', $post['customer'])
				   ->and_where_close()
				   ->group_by('polregs.type_id');
				   
				   
		$query = DB::select('t.type', 'k.merk', 'm.model', array($sub, 'jumlah'))->from('polregs as p')
						->join('types as t', 'inner')->on('t.id', '=', 'p.type_id')
						->join('merks as k', 'inner')->on('k.id', '=', 't.merk_id')
						->join('models as m', 'inner')->on('m.id', '=', 't.model_id');
		
		//$query = $query->where('merks.id', '=', $post['merk']);
		if(isset($post['model_id']))
		{
			if(count($models) > 0)
			{
				$query = $query->where_open();
				foreach($models as $model)
				{
					if($model > 0)
					{
							$query = $query->or_where('t.model_id', '=', $model);
					}
				}
				$query = $query->or_where_close();
			}
		}
		
		if(isset($post['merk_id']))
		{
			if(count($merks) > 0)
			{
				$query = $query->where_open();
				//echo $merks;
				foreach($merks as $merk)
				{
					if($merk > 0)
					{
							$query = $query->or_where('t.merk_id', '=', $merk);
					}
				}
				$query = $query->or_where_close();
			}
		}
		
		if(isset($post['type_id']))
		{
			if(count($types) > 0)
			{
				$query = $query->where_open();
				foreach($types as $type)
				{
					if($types > 0)
					{
							$query = $query->or_where('p.type_id', '=', $type);
					}
				}
				$query = $query->or_where_close();
			}
			
		}
		
		if($tahun == 0)
		{
			if(isset($post['drthn']) && isset($post['kethn']))
			{
				if($post['drthn']!="" && $post['kethn']!="" )
				{
					list($d, $m, $y) = preg_split('/\//', $_POST['drthn']); 
					list($d1, $m1, $y1) = preg_split('/\//', $_POST['kethn']);
					$query = $query->and_where_open();
					$query = $query->and_where(DB::expr('YEAR(p.tgl_daftar)'),'BETWEEN',array($y, $y1));
					$query = $query->and_where_close();
				}
			}
		}
		else 
		{
			$query = $query->where(DB::expr('YEAR(p.tgl_daftar)'), '=', $tahun);
		}
		
		//$query = $query->where(DB::expr('YEAR(tgl_daftar)'), '=', $tahun);
		
		$query = $query->where('p.customer_id', '=', $post['customer']);
	
		
		$query = $query->group_by('t.id, t.type, m.model, k.merk');
		$query = $query->order_by('jumlah', 'DESC');
		//print_r($query);
		return $query->execute();		
	
	}
	
	public function detail_customer_by_tahun($post, $tahun)
	{
		$models = array();
		$types = array();
		$merks = array();
		$areas = array();
		
		$sub = DB::select(array('COUNT("polregs.id")', 'jumlah'))->from('polregs')
			  ->where('polregs.type_id', '=', DB::expr('t.id'));
		
		if(isset($post['drthn']) && isset($post['kethn']))
		{
			if($post['drthn']!="" && $post['kethn']!="" )
			{
				$sub = $sub->and_where_open();
				$sub = $sub->and_where(DB::expr('YEAR(polregs.tgl_daftar)'),'=',$tahun);
				$sub = $sub->and_where_close();
			}
		}
		
		$sub = $sub->and_where_open();
		
		$sub = $sub->and_where('polregs.customer_id', '=', $post['customer'])
				   ->and_where_close()
				   ->group_by('polregs.type_id');
				   
				   
		$query = DB::select('t.type', 'k.merk', 'm.model', array($sub, 'jumlah'))->from('polregs as p')
						->join('types as t', 'inner')->on('t.id', '=', 'p.type_id')
						->join('merks as k', 'inner')->on('k.id', '=', 't.merk_id')
						->join('models as m', 'inner')->on('m.id', '=', 't.model_id');
		
		//$query = $query->where('merks.id', '=', $post['merk']);
		$query = $query->where(DB::expr('YEAR(tgl_daftar)'), '=', $tahun);
		
/*		if($tahun > 0)
		{
			if(isset($post['drthn']) && isset($post['kethn']))
			{
				if($post['drthn']!="" && $post['kethn']!="" )
				{
					$query = $query->and_where_open();
					$query = $query->and_where('p.tgl_daftar','BETWEEN',array($post['drthn'], $post['kethn']));
					$query = $query->and_where_close();
				}
			}
		}
		else 
		{
			$query = $query->where(DB::expr('YEAR(tgl_daftar)'), '=', $tahun);
		}*/
		
		$query = $query->where('p.customer_id', '=', $post['customer']);
	
		
		$query = $query->group_by('t.id, t.type, m.model, k.merk');
		$query = $query->order_by('jumlah', 'DESC');
		return $query->execute();		
	
	}
	
	public function databytahun($post, $tahun)
	{

		$sub = DB::select(array('COUNT("polregs.id")', 'jumlah'))->from('polregs')
						 ->join('types','INNER')->on('types.id', '=', 'polregs.type_id')
						 ->join('customers','INNER')->on('customers.id', '=', 'polregs.id');
						 
		if(isset($post['model_id']))
		{
			if($post['model_id']!="" && $post['model_id'] > 0)
			{
				$sub = $sub->where('types.model_id', '=', $post['model_id']);
			}
		}
		
		$sub = $sub->where('customers.area_id', '=', DB::expr('areas.id'))
					->where(DB::expr('YEAR(tgl_daftar)'), '=', $tahun)
					->group_by('customers.area_id');
		$query = DB::select('areas.id','areas.area', array($sub, 'jumlah'))->from('areas');
		
/*		if($post['jumlah']!="")
		{
			$query = $query->where($sub,'>=',$post['jumlah']);
		}*/
		$query = $query->order_by('jumlah', 'DESC');
		//print_r($query);
		return $query->execute();		
	}
	
	
	public function customerhistory($id, $tahun, $model)
	{

		$sub = DB::select('polregs.type_id', array('COUNT("polregs.type_id")', 'jumlah'))->from('polregs')
						 ->join('types','INNER')->on('types.id', '=', 'polregs.type_id')
						 ->join('customers','INNER')->on('customers.id', '=', 'polregs.id');
		
		$sub = $sub->where('polregs.customer_id', '=', $id)
					->where(DB::expr('YEAR(tgl_daftar)'), '=', $tahun);
		if($model > 0)
		{
			$sub = $sub->where('types.model_id', '=', $model);
		}
			$sub = $sub->group_by('polregs.type_id');
					
					//print_r($sub);
		
/*		if($post['jumlah']!="")
		{
			$query = $query->where($sub,'>=',$post['jumlah']);
		}*/
		
		return $sub->execute();		
	}
	
	public function history_tahun($customer)
	{
		$query = DB::select(array(DB::expr('YEAR(tgl_daftar)'), 'tahun'))->from('polregs')->where('customer_id', '=', $customer)->group_by(DB::expr('YEAR(tgl_daftar)'));
		
		return $query->execute();
	}
	
	public function data_detail_customer($id = 0)
	{
		if(isset($_POST))
		{
			if(isset($_POST['area_id']))
			{
				$area = $_POST['area_id'];
			}
		}
		$sub = DB::select(array('COUNT("type_id")', 'jumlah'))->from('polregs')
						 ->where('customer_id', '=', DB::expr('customers.id'))
						 ->group_by('type_id');
		if(isset($area))
		{
			//echo $area;
/*			$query = DB::select('customers.id','customers.nama','customers.alamat', 'types.type', 'merks.merk', array('COUNT("polregs.type_id")', 'jumlah'))->from('customers')
								->join('polregs', 'INNER')->on('polregs.customer_id', '=', 'customers.id' )
								->join('types', 'INNER')->on('types.id', '=', 'polregs.type_id')
								->join('merks', 'INNER')->on('merks.id', '=', 'polregs.merk_id')
								->where('customers.area_id','=',$area)->where('customers.id', '=',$id)
								->order_by('jumlah', 'DESC');*/
			$query = DB::select( 'types.type',array('COUNT("type_id")','jumlah'))->from('polregs')
								->join('customers', 'INNER')->on('customers.id','=','polregs.customer_id')
								->join('merks', 'INNER')->on('merks.id', '=','polregs.merk_id')
								->join('types', 'INNER')->on('types.id', '=', 'polregs.type_id')
								->group_by('polregs.type_id')
								->order_by('jumlah', 'DESC');
		}
		else 
		{
/*			$query = DB::select('customers.nama','customers.alamat', 'types.type', 'merks.merk', array('COUNT("polregs.type_id")', 'jumlah'))->from('customers')
								->join('polregs', 'INNER')->on('polregs.customer_id', '=', 'customers.id' )
								->join('types', 'INNER')->on('types.id', '=', 'polregs.type_id')
								->join('merks', 'INNER')->on('merks.id', '=', 'polregs.merk_id')
								->where('customers.id', '=',$id)
								->order_by('jumlah', 'DESC');*/
/*			$sub = DB::select('types.type')->from('types')->where('id', '=', DB::expr('polregs.type_id'));
			$sub2 = DB::select('merks.merk')->from('merks')->where('id', '=', DB::expr('polregs.merk_id'));*/
			$query = DB::select( 'types.type', 'merks.merk', 'models.model', array($sub, 'jumlah'))->from('polregs')
								->join('types', 'INNER')->on('types.id', '=', 'polregs.type_id')
								->join('merks', 'INNER')->on('merks.id', '=','types.merk_id')
								->join('models', 'INNER')->on('models.id', '=','types.model_id')
								->where('polregs.customer_id', '=', $id)
								->group_by('polregs.type_id')
								->order_by('jumlah', 'DESC');
		}
		//print_r($query);
		return $query->execute();		
	}
}